library(Lahman)
library(sqldf)
## Loading required package: gsubfn
## Loading required package: proto
## Loading required package: RSQLite

Level one header

leavel 2 header

level 3 header

You can type normally to write paragraphs that will appear in your documents

  • Item one in a list
  • Item 2
  • Item 3
  • Item 4

This is how to make a table with multiple columns

column 1 cloumn 2
Lions NFC
Patriots AFC
Buccaneers AFC
Eagles NFC

Baseball Facts First Fact, I absolutely hate baseball.

These are examples of ques run that gather different data and also show how we can organize how the information will be displayed, example the 4th one.

query<-"SELECT playerID,yearID,teamID,HR FROM Batting
WHERE teamID='PHI' and yearID>=1970 and yearID<=1979 and HR>= 30"
sqldf(query)
##     playerID yearID teamID HR
## 1  johnsde01   1971    PHI 34
## 2  montawi01   1971    PHI 30
## 3  schmimi01   1974    PHI 36
## 4  luzingr01   1975    PHI 34
## 5  schmimi01   1975    PHI 38
## 6  schmimi01   1976    PHI 38
## 7  luzingr01   1977    PHI 39
## 8  schmimi01   1977    PHI 38
## 9  luzingr01   1978    PHI 35
## 10 schmimi01   1979    PHI 45
query<-"SELECT playerID,yearID,teamID,HR FROM Batting
WHERE teamID='NYA' and HR> 40"
sqldf(query)
##     playerID yearID teamID HR
## 1   ruthba01   1920    NYA 54
## 2   ruthba01   1921    NYA 59
## 3   ruthba01   1923    NYA 41
## 4   ruthba01   1924    NYA 46
## 5   ruthba01   1926    NYA 47
## 6  gehrilo01   1927    NYA 47
## 7   ruthba01   1927    NYA 60
## 8   ruthba01   1928    NYA 54
## 9   ruthba01   1929    NYA 46
## 10 gehrilo01   1930    NYA 41
## 11  ruthba01   1930    NYA 49
## 12 gehrilo01   1931    NYA 46
## 13  ruthba01   1931    NYA 46
## 14  ruthba01   1932    NYA 41
## 15 gehrilo01   1934    NYA 49
## 16 gehrilo01   1936    NYA 49
## 17 dimagjo01   1937    NYA 46
## 18 mantlmi01   1956    NYA 52
## 19 mantlmi01   1958    NYA 42
## 20 mantlmi01   1961    NYA 54
## 21 marisro01   1961    NYA 61
## 22 jacksre01   1980    NYA 41
## 23 martiti02   1997    NYA 44
## 24 giambja01   2002    NYA 41
## 25 giambja01   2003    NYA 41
## 26 rodrial01   2005    NYA 48
## 27 rodrial01   2007    NYA 54
## 28 grandcu01   2011    NYA 41
## 29 grandcu01   2012    NYA 43
query<-"SELECT playerID,yearID,teamID,HR,SO FROM Batting
WHERE HR> 40 and SO<=60"
sqldf(query)
##     playerID yearID teamID HR SO
## 1  hornsro01   1922    SLN 42 50
## 2  willicy01   1923    PHI 41 57
## 3    ottme01   1929    NY1 42 38
## 4   ruthba01   1929    NYA 46 60
## 5  gehrilo01   1931    NYA 46 56
## 6   ruthba01   1931    NYA 46 51
## 7  gehrilo01   1934    NYA 49 31
## 8  gehrilo01   1936    NYA 49 46
## 9  troskha01   1936    CLE 42 58
## 10 dimagjo01   1937    NYA 46 37
## 11  mizejo01   1940    SLN 43 49
## 12  mizejo01   1947    NY1 51 42
## 13 willite01   1949    BOS 43 48
## 14 kinerra01   1951    PIT 42 57
## 15 camparo01   1953    BRO 41 58
## 16 rosenal01   1953    CLE 43 48
## 17 kluszte01   1954    CIN 49 35
## 18  mayswi01   1954    NY1 41 57
## 19 kluszte01   1955    CIN 47 40
## 20  mayswi01   1955    NY1 51 60
## 21 aaronha01   1957    ML1 44 58
## 22 sievero01   1957    WS1 42 55
## 23 aaronha01   1969    ATL 44 47
## 24 aaronha01   1971    ATL 47 58
## 25 thomafr04   1993    CHA 41 54
## 26 bondsba01   2002    SFN 46 47
## 27 bondsba01   2003    SFN 45 58
## 28 bondsba01   2004    SFN 45 41
## 29 pujolal01   2004    SLN 46 52
## 30 pujolal01   2006    SLN 49 50
query<-"SELECT playerID,teamID,yearID,HR FROM Batting
WHERE HR>50
ORDER BY HR DESC"
sqldf(query)
##     playerID teamID yearID HR
## 1  bondsba01    SFN   2001 73
## 2  mcgwima01    SLN   1998 70
## 3   sosasa01    CHN   1998 66
## 4  mcgwima01    SLN   1999 65
## 5   sosasa01    CHN   2001 64
## 6   sosasa01    CHN   1999 63
## 7  marisro01    NYA   1961 61
## 8   ruthba01    NYA   1927 60
## 9   ruthba01    NYA   1921 59
## 10  foxxji01    PHA   1932 58
## 11 greenha01    DET   1938 58
## 12 howarry01    PHI   2006 58
## 13 gonzalu01    ARI   2001 57
## 14 rodrial01    TEX   2002 57
## 15 wilsoha01    CHN   1930 56
## 16 griffke02    SEA   1997 56
## 17 griffke02    SEA   1998 56
## 18  ruthba01    NYA   1920 54
## 19  ruthba01    NYA   1928 54
## 20 kinerra01    PIT   1949 54
## 21 mantlmi01    NYA   1961 54
## 22 ortizda01    BOS   2006 54
## 23 rodrial01    NYA   2007 54
## 24 bautijo02    TOR   2010 54
## 25 davisch02    BAL   2013 53
## 26 mantlmi01    NYA   1956 52
## 27  mayswi01    SFN   1965 52
## 28 fostege01    CIN   1977 52
## 29 mcgwima01    OAK   1996 52
## 30 rodrial01    TEX   2001 52
## 31 thomeji01    CLE   2002 52
## 32 kinerra01    PIT   1947 51
## 33  mizejo01    NY1   1947 51
## 34  mayswi01    NY1   1955 51
## 35 fieldce01    DET   1990 51
## 36 jonesan01    ATL   2005 51
# This finds and sorts who has had the fewest strikeouts in a season with atleast 400 at bats
query<-"SELECT teamID, playerID, yearID, SO, AB FROM Batting
WHERE AB>= 400 and SO< 10
ORDER BY SO"
sqldf(query)
##    teamID  playerID yearID SO  AB
## 1     NY1 doyleja01   1894  3 422
## 2     NYA seweljo01   1932  3 503
## 3     CLE seweljo01   1925  4 608
## 4     CLE seweljo01   1929  4 578
## 5     NYA seweljo01   1933  4 524
## 6     NY1  wardjo01   1893  5 588
## 7     CHN holloch01   1922  5 592
## 8     CLE mcinnst01   1922  5 537
## 9     PIT wanerll01   1936  5 414
## 10    BS1 wrighge01   1875  6 408
## 11    BSN broutda01   1889  6 485
## 12    BLN keelewi01   1894  6 590
## 13    NY1  wardjo01   1894  6 540
## 14    SLN quinnjo02   1895  6 543
## 15    BSN mcinnst01   1924  6 581
## 16    CLE seweljo01   1926  6 578
## 17    NY1  wardjo01   1889  7 479
## 18    PHI crossla01   1893  7 415
## 19    SLN quinnjo02   1893  7 547
## 20    PHI crossla01   1894  7 529
## 21    CIN vaughfa01   1896  7 433
## 22    PHA cochrmi01   1927  7 432
## 23    CLE seweljo01   1927  7 569
## 24    PIT traynpi01   1929  7 540
## 25    NY1 muelldo01   1956  7 453
## 26    NY1 connoro01   1885  8 455
## 27    IN3 glassja01   1887  8 483
## 28    NY1 glassja01   1890  8 512
## 29    PIT donovpa01   1893  8 499
## 30    CHN dungasa01   1893  8 465
## 31    LS3 pinknge01   1893  8 446
## 32    BLN brodist01   1894  8 573
## 33    SLN quinnjo02   1894  8 405
## 34    PIT bierblo01   1895  8 466
## 35    PHI crossla01   1895  8 535
## 36    CIN   hoydu01   1895  8 429
## 37    CIN roushed01   1921  8 418
## 38    CHA collied01   1923  8 505
## 39    CHA collied01   1925  8 425
## 40    WS1 speaktr01   1927  8 523
## 41    PHA cochrmi01   1929  8 514
## 42    NYA seweljo01   1931  8 484
## 43    PIT wanerll01   1933  8 500
## 44    PHI verbaem01   1947  8 540
## 45    CHN ansonca01   1883  9 413
## 46    DTN broutda01   1887  9 500
## 47    WAS   hoydu01   1893  9 564
## 48    PIT bierblo01   1894  9 525
## 49    BLN broutda01   1894  9 525
## 50    SLN milledo01   1894  9 481
## 51    BLN keelewi01   1896  9 544
## 52    CL4 mckeaed01   1896  9 571
## 53    CLE speaktr01   1918  9 471
## 54    CHN  dealch01   1921  9 422
## 55    BOS mcinnst01   1921  9 584
## 56    SLA severha01   1921  9 472
## 57    BSN  highan01   1926  9 476
## 58    CLE summaho01   1926  9 581
## 59    CLE seweljo01   1928  9 588
## 60    WS1  ricesa01   1929  9 616
## 61    NY1 leachfr01   1931  9 515
## 62    PHA busched01   1945  9 416
## 63    BSN holmeto01   1945  9 636
## 64    CLE boudrlo01   1948  9 560
## 65    CLE mitchda01   1952  9 511
query<-"SELECT playerID,sum(HR) FROM Batting
WHERE playerID='ruthba01'
GROUP BY playerID"
sqldf(query)
##   playerID sum(HR)
## 1 ruthba01     714
#Filter before group is where, and filter after group uses having
query<-"SELECT playerID,sum(HR) FROM Batting
GROUP BY playerID
HAVING sum(HR)>= 600
ORDER BY sum(HR) DESC"
sqldf(query)
##    playerID sum(HR)
## 1 bondsba01     762
## 2 aaronha01     755
## 3  ruthba01     714
## 4 rodrial01     696
## 5  mayswi01     660
## 6 griffke02     630
## 7 thomeji01     612
## 8  sosasa01     609
#sum() avg() max() min() are all useable fucntions
query<-"SELECT playerID,avg(HR) FROM Batting
GROUP BY playerID
HAVING avg(HR)>= 30
ORDER BY avg(HR) DESC"
sqldf(query)
##     playerID  avg(HR)
## 1  pujolal01 36.93750
## 2  bondsba01 34.63636
## 3  mcgwima01 34.29412
## 4  kinerra01 33.54545
## 5  aaronha01 32.82609
## 6  bryankr01 32.50000
## 7   ruthba01 32.45455
## 8   sosasa01 32.05263
## 9  cabremi01 31.85714
## 10 belleal01 31.75000
## 11 rodrial01 31.63636
## 12 schmimi01 30.44444
## 13 abreujo02 30.33333
#This is us testing how to draw from multiple index at one
#Time and combine responses from these
query<-"SELECT nameFirst, nameLast, teamID, yearID, HR
FROM Batting INNER JOIN Master
On Batting.playerID=Master.playerID
WHERE Batting.playerID='ruthba01'"
sqldf(query)
##    nameFirst nameLast teamID yearID HR
## 1       Babe     Ruth    BOS   1914  0
## 2       Babe     Ruth    BOS   1915  4
## 3       Babe     Ruth    BOS   1916  3
## 4       Babe     Ruth    BOS   1917  2
## 5       Babe     Ruth    BOS   1918 11
## 6       Babe     Ruth    BOS   1919 29
## 7       Babe     Ruth    NYA   1920 54
## 8       Babe     Ruth    NYA   1921 59
## 9       Babe     Ruth    NYA   1922 35
## 10      Babe     Ruth    NYA   1923 41
## 11      Babe     Ruth    NYA   1924 46
## 12      Babe     Ruth    NYA   1925 25
## 13      Babe     Ruth    NYA   1926 47
## 14      Babe     Ruth    NYA   1927 60
## 15      Babe     Ruth    NYA   1928 54
## 16      Babe     Ruth    NYA   1929 46
## 17      Babe     Ruth    NYA   1930 49
## 18      Babe     Ruth    NYA   1931 46
## 19      Babe     Ruth    NYA   1932 41
## 20      Babe     Ruth    NYA   1933 34
## 21      Babe     Ruth    NYA   1934 22
## 22      Babe     Ruth    BSN   1935  6
#Draw from two queries

#Drew from two querries and replaced playerID with
#Real name
query<-"SELECT nameFirst, nameLast, teamID, yearID, HR
FROM Batting INNER JOIN Master
On Batting.playerID=Master.playerID
WHERE HR> 50
ORDER BY HR DESC"
sqldf(query)
##    nameFirst  nameLast teamID yearID HR
## 1      Barry     Bonds    SFN   2001 73
## 2       Mark   McGwire    SLN   1998 70
## 3      Sammy      Sosa    CHN   1998 66
## 4       Mark   McGwire    SLN   1999 65
## 5      Sammy      Sosa    CHN   2001 64
## 6      Sammy      Sosa    CHN   1999 63
## 7      Roger     Maris    NYA   1961 61
## 8       Babe      Ruth    NYA   1927 60
## 9       Babe      Ruth    NYA   1921 59
## 10    Jimmie      Foxx    PHA   1932 58
## 11      Hank Greenberg    DET   1938 58
## 12      Ryan    Howard    PHI   2006 58
## 13      Luis  Gonzalez    ARI   2001 57
## 14      Alex Rodriguez    TEX   2002 57
## 15      Hack    Wilson    CHN   1930 56
## 16       Ken   Griffey    SEA   1997 56
## 17       Ken   Griffey    SEA   1998 56
## 18      Babe      Ruth    NYA   1920 54
## 19      Babe      Ruth    NYA   1928 54
## 20     Ralph     Kiner    PIT   1949 54
## 21    Mickey    Mantle    NYA   1961 54
## 22     David     Ortiz    BOS   2006 54
## 23      Alex Rodriguez    NYA   2007 54
## 24      Jose  Bautista    TOR   2010 54
## 25     Chris     Davis    BAL   2013 53
## 26    Mickey    Mantle    NYA   1956 52
## 27    Willie      Mays    SFN   1965 52
## 28    George    Foster    CIN   1977 52
## 29      Mark   McGwire    OAK   1996 52
## 30      Alex Rodriguez    TEX   2001 52
## 31       Jim     Thome    CLE   2002 52
## 32     Ralph     Kiner    PIT   1947 51
## 33    Johnny      Mize    NY1   1947 51
## 34    Willie      Mays    NY1   1955 51
## 35     Cecil   Fielder    DET   1990 51
## 36    Andruw     Jones    ATL   2005 51
query<-"SELECT playerID,name,Batting.yearID, Batting.HR
FROM Batting INNER JOIN Teams
On Batting.teamID=Teams.teamID and Batting.yearID=Teams.yearID
WHERE playerID='ruthba01'"
sqldf(query)
##    playerID             name yearID HR
## 1  ruthba01   Boston Red Sox   1914  0
## 2  ruthba01   Boston Red Sox   1915  4
## 3  ruthba01   Boston Red Sox   1916  3
## 4  ruthba01   Boston Red Sox   1917  2
## 5  ruthba01   Boston Red Sox   1918 11
## 6  ruthba01   Boston Red Sox   1919 29
## 7  ruthba01 New York Yankees   1920 54
## 8  ruthba01 New York Yankees   1921 59
## 9  ruthba01 New York Yankees   1922 35
## 10 ruthba01 New York Yankees   1923 41
## 11 ruthba01 New York Yankees   1924 46
## 12 ruthba01 New York Yankees   1925 25
## 13 ruthba01 New York Yankees   1926 47
## 14 ruthba01 New York Yankees   1927 60
## 15 ruthba01 New York Yankees   1928 54
## 16 ruthba01 New York Yankees   1929 46
## 17 ruthba01 New York Yankees   1930 49
## 18 ruthba01 New York Yankees   1931 46
## 19 ruthba01 New York Yankees   1932 41
## 20 ruthba01 New York Yankees   1933 34
## 21 ruthba01 New York Yankees   1934 22
## 22 ruthba01    Boston Braves   1935  6